﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using Agresso.Interface.Layer.Domain;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using System.Data;
using System.Collections;

namespace Agresso.Interface.Layer.Service
{
    public class ChargeTypeAccountMappingService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        AlertService m_alertService;

        public ChargeTypeAccountMappingService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
            m_alertService = new AlertService();
        }
        public ChargeTypeAccountMappingService()
        {
            m_alertService = new AlertService();
            m_dataBaseConectivity = new DataBaseConnectivity();
        }
        public IList GetAllChargeTypeAccountMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(ChargeTypeAccountMappingSQL.GetAllChargeTypeAccountMapping(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result.Add(new ChargeTypeAccountMapping(rd));
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        //public bool IsValidChargeTypeAccountMapping(string chargeTypeName)
        //{
        //    try
        //    {
        //        m_dataBaseConectivity.InterfaceSqlConnection.Open();
        //        m_dataBaseConectivity.SoftmarSqlConnection.Open();
        //        //m_dataBaseConectivity.AgressoSqlConnection.Open();
        //        //find pc in pc mapping table-------------
        //        SqlCommand cmd = new SqlCommand(ChargeTypeAccountMappingSQL.GetChargeTypeAccountMappingByName(), m_dataBaseConectivity.InterfaceSqlConnection);
        //        cmd.Parameters.Add("@charge_type", SqlDbType.NVarChar).Value = chargeTypeName;
        //        SqlDataReader rd = cmd.ExecuteReader();
        //        ChargeTypeAccountMapping chargeType = null;
        //        if (rd.HasRows)
        //        {
        //            rd.Read();
        //            chargeType = new ChargeTypeAccountMapping(rd);
        //        }
        //        rd.Close();
        //        if (chargeType == null)
        //        {
        //            //research all from softmar
        //            cmd.Parameters.Clear();
        //            cmd.CommandText = ChargeTypeAccountMappingSQL.GetAllSoftmarChargeTypeAccount();
        //            cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
        //            rd = cmd.ExecuteReader();
        //            IList chargeAccounts = new ArrayList();
        //            if (rd.HasRows)
        //            {
        //                while (rd.Read())
        //                {
        //                   ChargeTypeAccountMapping ct = new ChargeTypeAccountMapping(true, rd);
        //                   chargeAccounts.Add(ct);
        //                }
        //            }
        //            rd.Close();
        //            foreach (ChargeTypeAccountMapping c in chargeAccounts)
        //            {
        //                cmd.Parameters.Clear();
        //                cmd.CommandText = ChargeTypeAccountMappingSQL.GetChargeTypeAccountMappingByName();
        //                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
        //                cmd.Parameters.Add("@charge_type", SqlDbType.NVarChar).Value = c.charge_type;
        //                rd = cmd.ExecuteReader();
        //                bool found = rd.HasRows;
        //                rd.Close();
        //                if (!found)
        //                {
        //                    cmd.Parameters.Clear();
        //                    cmd.CommandText = ChargeTypeAccountMappingSQL.InsertChargeTypeAccountMapping();
        //                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
        //                    cmd.Parameters.Add("charge_type", SqlDbType.NVarChar).Value = c.charge_type;
        //                    cmd.Parameters.Add("account", SqlDbType.NVarChar).Value = c.account;
        //                    cmd.Parameters.Add("tcrelet_account", SqlDbType.NVarChar).Value = c.tcrelet_account;
        //                    cmd.Parameters.Add("vcrelet_account", SqlDbType.NVarChar).Value = c.vcrelet_account;
        //                    cmd.Parameters.Add("freighthire_account", SqlDbType.NVarChar).Value = c.freighthire_account;
        //                    cmd.Parameters.Add("tcrelet_tax_account", SqlDbType.NVarChar).Value = c.tcrelet_tax_account;
        //                    cmd.Parameters.Add("vcrelet_tax_account", SqlDbType.NVarChar).Value = c.vcrelet_tax_account;
        //                    cmd.Parameters.Add("freighthire_tax_account", SqlDbType.NVarChar).Value = c.freighthire_tax_account;
        //                    cmd.Parameters.Add("created_date", SqlDbType.NVarChar).Value = DateTime.Now;
        //                    cmd.Parameters.Add("modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
        //                    cmd.ExecuteNonQuery();
        //                }
        //            }
        //        }
        //        //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
        //        return chargeType == null ? false : chargeType.account != string.Empty;
        //    }
        //    catch (Exception x)
        //    {
        //        LogService.WriteLogException(x);
        //    }
        //    finally
        //    {
        //        m_dataBaseConectivity.InterfaceSqlConnection.Close();
        //        m_dataBaseConectivity.SoftmarSqlConnection.Close();
        //    }
        //    return false;
        //}

        public ChargeTypeAccountMapping GetChargeTypeAccountMapping(string chargeTypeName)
        {
            ChargeTypeAccountMapping chargeType = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //find pc in pc mapping table-------------
                SqlCommand cmd = new SqlCommand(ChargeTypeAccountMappingSQL.GetChargeTypeAccountMappingByName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@charge_type", SqlDbType.NVarChar).Value = chargeTypeName;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    chargeType = new ChargeTypeAccountMapping(rd);
                }
                rd.Close();
                if (chargeType == null)
                {
                    chargeType = new ChargeTypeAccountMapping();
                    chargeType.charge_type = chargeTypeName;
                    cmd.Parameters.Clear();
                    cmd.CommandText = ChargeTypeAccountMappingSQL.InsertChargeTypeAccountMapping();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Add("charge_type", SqlDbType.NVarChar).Value = chargeType.charge_type;
                    cmd.Parameters.Add("account", SqlDbType.NVarChar).Value = "";
                    cmd.Parameters.Add("created_date", SqlDbType.NVarChar).Value = DateTime.Now;
                    cmd.Parameters.Add("modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                    cmd.Parameters.Add("tcrelet_account", SqlDbType.NVarChar).Value = chargeType.tcrelet_account;
                    cmd.Parameters.Add("vcrelet_account", SqlDbType.NVarChar).Value = chargeType.bareboatrelet_account;
                    cmd.Parameters.Add("freighthire_account", SqlDbType.NVarChar).Value = chargeType.chartererInv_account;
                    cmd.Parameters.Add("tcrelet_tax_account", SqlDbType.NVarChar).Value = chargeType.tcrelet_tax_account_ovs;
                    cmd.Parameters.Add("vcrelet_tax_account", SqlDbType.NVarChar).Value = chargeType.bareboatrelet_tax_account_ovs;
                    cmd.Parameters.Add("freighthire_tax_account", SqlDbType.NVarChar).Value = chargeType.chartererInv_tax_account;
                    cmd.Parameters.Add("tcrelet_account_name", SqlDbType.NVarChar).Value = chargeType.tcrelet_account_name;
                    cmd.Parameters.Add("tcrelet_tax_account_singapore", SqlDbType.NVarChar).Value = chargeType.tcrelet_tax_account_singapore;
                    cmd.Parameters.Add("vcrelet_tax_account_singapore", SqlDbType.NVarChar).Value = chargeType.bareboatrelet_tax_account_singapore;
                    cmd.Parameters.Add("vcrelet_account_name", SqlDbType.NVarChar).Value = chargeType.bareboatrelet_account_name;
                    cmd.Parameters.Add("freighthire_account_name", SqlDbType.NVarChar).Value = chargeType.chartererInv_account_name;
                    cmd.ExecuteNonQuery();
                }
                //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
                //return chargeType == null ? false : chargeType.account != string.Empty;
                return chargeType;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
           // return chargeType;
        }
        public bool AlertIfNotValidChargeType(ChargeTypeAccountMapping chargeType, string key, AlertType alertType)
        {
            bool valid = false;
            if (chargeType != null)
            {
                valid = chargeType.IsValidChargeTypeMapping();
                if (valid)
                    m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.RESOLVED, "Please update Agresso map for ChargeType with name : " + chargeType.charge_type, "Charge Type");
                else
                    m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.PENDING, "Please update Agresso map for ChargeType with name : " + chargeType.charge_type, "Charge Type");
            }
            return valid;
        }
        public void UpdateChargeTypeAccountMapping(ChargeTypeAccountMapping chargeType)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = ChargeTypeAccountMappingSQL.UpdateChargeTypeAccountMapping();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@cta_id", SqlDbType.Int).Value = chargeType.cta_id;
                cmd.Parameters.Add("@charge_type", SqlDbType.NVarChar).Value = chargeType.charge_type;
                cmd.Parameters.Add("@account", SqlDbType.NVarChar).Value = chargeType.account;
                cmd.Parameters.Add("tcrelet_account", SqlDbType.NVarChar).Value = chargeType.tcrelet_account;
                cmd.Parameters.Add("vcrelet_account", SqlDbType.NVarChar).Value = chargeType.bareboatrelet_account;
                cmd.Parameters.Add("freighthire_account", SqlDbType.NVarChar).Value = chargeType.chartererInv_account;
                cmd.Parameters.Add("tcrelet_tax_account", SqlDbType.NVarChar).Value = chargeType.tcrelet_tax_account_ovs;
                cmd.Parameters.Add("vcrelet_tax_account", SqlDbType.NVarChar).Value = chargeType.bareboatrelet_tax_account_ovs;
                cmd.Parameters.Add("freighthire_tax_account", SqlDbType.NVarChar).Value = chargeType.chartererInv_tax_account;
                cmd.Parameters.Add("tcrelet_account_name", SqlDbType.NVarChar).Value = chargeType.tcrelet_account_name;
                cmd.Parameters.Add("vcrelet_account_name", SqlDbType.NVarChar).Value = chargeType.bareboatrelet_account_name;
                cmd.Parameters.Add("tcrelet_tax_account_singapore", SqlDbType.NVarChar).Value = chargeType.tcrelet_tax_account_singapore;
                cmd.Parameters.Add("vcrelet_tax_account_singapore", SqlDbType.NVarChar).Value = chargeType.bareboatrelet_tax_account_singapore;
                cmd.Parameters.Add("freighthire_account_name", SqlDbType.NVarChar).Value = chargeType.chartererInv_account_name;

                cmd.Parameters.Add("cargorelet_account_name_IN", SqlDbType.NVarChar).Value = chargeType.cargorelet_account_IN_name;
                cmd.Parameters.Add("cargorelet_account_IN", SqlDbType.NVarChar).Value = chargeType.cargorelet_account_IN;
                cmd.Parameters.Add("cargorelet_account_GST_IN", SqlDbType.NVarChar).Value = chargeType.cargorelet_account_IN_GST;

                cmd.Parameters.Add("cargorelet_account_OUT_Ovs", SqlDbType.NVarChar).Value = chargeType.cargorelet_account_OUT_Ovs;
                cmd.Parameters.Add("cargorelet_account_name_OUT_Ovs", SqlDbType.NVarChar).Value = chargeType.cargorelet_account_OUT_Ovs_name;
                cmd.Parameters.Add("cargorelet_account_GST_OUT_Ovs", SqlDbType.NVarChar).Value = chargeType.cargorelet_account_OUT_Ovs_GST;

                cmd.Parameters.Add("cargorelet_account_OUT_SG", SqlDbType.NVarChar).Value = chargeType.cargorelet_account_OUT_SG;
                cmd.Parameters.Add("cargorelet_account_name_OUT_SG", SqlDbType.NVarChar).Value = chargeType.cargorelet_account_OUT_SG_name;
                cmd.Parameters.Add("cargorelet_account_GST_OUT_SG", SqlDbType.NVarChar).Value = chargeType.cargorelet_account_OUT_SG_GST;

                cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
    }
}
